package fr.greta.centre.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import javax.naming.NamingException;

import fr.greta.centre.metier.Candidat;
import fr.greta.centre.metier.Candidature;


public class CandidatDAOImpl implements CandidatDAO {

	/**
	 * Insere le candidat dans la base de donnees. Requiert candidat != null.
	 * 
	 * TODO : cette methode doit renvoyer la SQLException, et utiliser
	 * getGeneratedKeys pour avoir le id du candidat, ou bien (moins efficace)
	 * faire une transaction englobant le insert et le select, sous peine de
	 * recuperer un autre id que celui genere, si d'autres insertions se sont
	 * glisses entre le insert et le select.
	 * */
	public int save(Candidat candidat) throws SQLException {
		Connection connexion;
		try {
			connexion = Bd.getConnect();
			int result = save(candidat, connexion);
			connexion.close();
			return result;
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	/** Insertion d'un nouveau candidat.
	 * Son mot de passe est g�n�r� al�atoirement sur 6 positions.
	 * @param candidat
	 * @param connexion
	 * @return
	 * @throws SQLException
	 */
	public int save(Candidat candidat, Connection connexion)
			throws SQLException {
		
		// Le prerequis
		assert candidat != null;
		candidat.setPwd(getPwdAleatoire());
		int i = 1;
		try {
			String sql = "INSERT INTO centre_formation.candidat (nom,prenom,email,pwd,tel,cp,ville,adresse) "
					+ " VALUES (?,?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement st = connexion.prepareStatement(sql);
			st.setString(1,  candidat.getNom());
			st.setString(2,  candidat.getPrenom());
			st.setString(3,  candidat.getEmail());
			st.setString(4,  candidat.getPwd());
			st.setString(5,  candidat.getTel());
			st.setString(6,  candidat.getCp());
			st.setString(7,  candidat.getVille());
			st.setString(8,  candidat.getAdresse());
			st.executeUpdate();

			String ss = "SELECT MAX(id_candidat) AS LastID FROM candidat";
			ResultSet rs = st.executeQuery(ss);
			rs.next();
			candidat.setId(rs.getInt(i));
		}
		catch (SQLException e) {
			System.out.println(e.getMessage());
			throw e;
		}
		return 0;
	}
	
	public int saveUrlPhoto(String url) throws SQLException{
	
		
		Connection connec;
		try {
			connec = Bd.getConnect();
			Statement statement = connec.createStatement();
			String sql = "UPDATE `centre_formation`.`candidat` SET `photo` ='"+url+"' WHERE `candidat`.`id_candidat` =8";
			System.out.println(sql);
			statement.executeUpdate(sql);
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return 0;
	}
	
	/** Cree un mot de passe sur 6 positions */
	private String getPwdAleatoire() {
		String tokens = "abcdefghijklmnopqrstuvwxyz0123456789";
		StringBuffer sb = new StringBuffer();
		Random hasard = new Random();
		for (int i=0; i<6 ;i++) {
			sb.append(tokens.charAt(hasard.nextInt(36)));
		}
		return sb.toString();
	}

	public boolean delete(int idPersonne) {

		try {
			Connection connec = Bd.getConnect();
			Statement statement = connec.createStatement();
			String sql = "DELETE FROM candidat where id_candidat = "
					+ idPersonne + "";
			statement.executeUpdate(sql);

			return true;
		} catch (SQLException e) {
			System.out.println(e.getMessage());

		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public Candidat findByKey(int idPersonne) {

		Candidat candidat = null;

		try {
			Connection connec = Bd.getConnect();
			Statement statement = connec.createStatement();
			String sql = "SELECT * FROM candidat WHERE id_candidat = "
					+ idPersonne + "";
			ResultSet resultat = statement.executeQuery(sql);
			resultat.next();
			List<Candidature> lesCandidatures = new ArrayList<Candidature>();
			candidat = new Candidat(resultat.getInt("id_candidat"),
					resultat.getString("nom"), resultat.getString("prenom"),
					resultat.getString("tel"), resultat.getString("email"),
					resultat.getString("ville"), resultat.getString("adresse"),
					resultat.getString("cp"), resultat.getString("pwd"),
					lesCandidatures);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		catch(NamingException e){
			e.printStackTrace();
		}

		return candidat;

	}

	public List<Candidat> findAll() throws SQLException {

		
		ArrayList<Candidat> lesCandidats = new ArrayList<Candidat>();

		try {
			Connection connect = Bd.getConnect();
			String sql = "SELECT * FROM candidat ";
			ResultSet resultat;
			resultat = connect.createStatement().executeQuery(sql);
			while (resultat.next()) {

				Candidat unCandidat = new Candidat(
						resultat.getInt("id_candidat"),
						resultat.getString("nom"),
						resultat.getString("prenom"),
						resultat.getString("tel"), resultat.getString("email"),
						resultat.getString("ville"),
						resultat.getString("adresse"),
						resultat.getString("cp"), resultat.getString("pwd"));
				lesCandidats.add(unCandidat);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return lesCandidats;
	}

	/**
	 * Renvoie null si le candidat n'est pas trouv�.
	 * 
	 * Prerequis : email non null et non vide, password non null et non vide.
	 */
	public Candidat findByEmailAndPassword(String email, String password)
			throws SQLException {
		// Prerequis (doit etre verifie avant l'appel) :
		assert email != null && email.matches("^(\\s)*$");
		assert password != null && password.matches("^(\\s)*$");
		Candidat candidat = null;
		PreparedStatement st;
		try {
			Connection conn = Bd.getConnect();
			String sql = "SELECT * FROM candidat WHERE email=? AND pwd=?";
			st = conn.prepareStatement(sql);
			st.setString(1, email);
			st.setString(2, password);
			ResultSet resultat = st.executeQuery();

			// TODO implementer findLesCandidatures()
			List<Candidature> lesCandidatures = new ArrayList<Candidature>();

			if (resultat.next()) {
				candidat = new Candidat(resultat.getInt("id_candidat"),
						resultat.getString("nom"),
						resultat.getString("prenom"),
						resultat.getString("tel"), resultat.getString("email"),
						resultat.getString("ville"),
						resultat.getString("adresse"),
						resultat.getString("cp"), resultat.getString("pwd"),
						lesCandidatures);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return candidat;
	}
	/** findByEmailrenvoi un candidat null si il n'est pas trouv�
	 * sinon on a un probleme de pointer (pointer exception)
	 */
	public Candidat findByEmail(String email) throws SQLException {
		Candidat unCandidat = null;
		String sql = "SELECT * FROM candidat WHERE email = '" + email + "'";
		Connection con;
		try {
			con = Bd.getConnect();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				unCandidat = new Candidat(rs.getInt("id_candidat"),
						rs.getString("nom"), rs.getString("prenom"),
						rs.getString("tel"), rs.getString("email"),
						rs.getString("ville"), rs.getString("adresse"),
						rs.getString("cp"), rs.getString("pwd"),
						null);
			}
			} catch (NamingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}


			return unCandidat;
		}



	public List<Candidature> getLesCandidatures(int idCandidat) {
		// TODO
		return null;

	}

	public boolean updateCandidat(Candidat candidat) throws SQLException {
		try {
			String sql = "UPDATE candidat SET nom=?, prenom=?, tel=?, email=?, ville=?, adresse=?, cp=?, pwd=?"
					+ " WHERE id_candidat = ?";
			PreparedStatement st = Bd.getConnect().prepareStatement(sql);
			st.setString(1, candidat.getNom());
			st.setString(2, candidat.getPrenom());
			st.setString(3, candidat.getTel());
			st.setString(4, candidat.getEmail());
			st.setString(5, candidat.getVille());
			st.setString(6, candidat.getAdresse());
			st.setString(7, candidat.getCp());
			st.setString(8, candidat.getPwd());
			st.setInt(9, candidat.getId());
			st.executeUpdate();
			

		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (NamingException e) {
			e.printStackTrace();
		}
		return true;
	}

	public void mettreAjour(Candidat candidat, Connection connexion) {
		// TODO Auto-generated method stub
		
	}

}
